package hn.cch.dao.impl;

import hn.cch.bean.Bean;
import hn.cch.constant.Path;
import hn.cch.dao.Dao;
import hn.cch.dao.po.Test;
import hn.cch.dao.api.TestDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class TestDaoImpl extends Dao implements TestDao {

    private static Logger logger = LoggerFactory.getLogger(TestDaoImpl.class);

    /**
     * 插入一行记录到数据库表格中
     * @param test
     * @return
     */
    public int insert(Test test) {

        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO test(test0, test1, test2, test3, test4, test5, test6, test7, test8, test9) ");
        sb.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        String sql = sb.toString();
        logger.info("insert_sql : " + sql);
        Object[] args = new Object[]{
                test.getTest0(),
                test.getTest1(),
                test.getTest2(),
                test.getTest3(),
                test.getTest4(),
                test.getTest5(),
                test.getTest6(),
                test.getTest7(),
                test.getTest8(),
                test.getTest9()
        };
        int rows = super.getJdbcTemplate().update(sql, args);
        return rows;
    }

    /**
     *
     * @param test
     * @return
     */
    public int update(Test test) {
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE test ");
        sb.append("SET test1 = ? ");
        sb.append(", test2 = ? ");
        sb.append(", test3 = ? ");
        sb.append(", test4 = ? ");
        sb.append(", test5 = ? ");
        sb.append(", test6 = ? ");
        sb.append(", test7 = ? ");
        sb.append(", test8 = ? ");
        sb.append(", test9 = ? ");
        sb.append("WHERE test0 = ? ");//主键
        String sql = sb.toString();
        logger.info("update_sql : " + sql);
        Object[] args = new Object[]{
                test.getTest1(),
                test.getTest2(),
                test.getTest3(),
                test.getTest4(),
                test.getTest5(),
                test.getTest6(),
                test.getTest7(),
                test.getTest8(),
                test.getTest9(),
                test.getTest0()//主键
        };
        int rows = super.getJdbcTemplate().update(sql, args);
        return rows;

    }

    public int delete(Test test) {
        StringBuffer sb = new StringBuffer();
        sb.append("DELETE FROM test ");
        sb.append("WHERE test0 = ? ");//主键
        String sql = sb.toString();
        logger.info("delete_sql : " + sql);
        Object[] args = new Object[]{
                test.getTest0(),
                  };
        int rows = super.getJdbcTemplate().update(sql, args);
        return rows;

    }

    /**
     * 查询记录列表
     *
     * @return
     */
    public List<Test> selectListWhere() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * ");
        sb.append("FROM test ");
        sb.append("WHERE 1 = 1 ");
        String sql = sb.toString();
        logger.info("select_sql : " + sql);
        RowMapper<Test> rowMapper = new TestRowMapper();
        List<Test> testList = super.getJdbcTemplate().query(sql, rowMapper);
        return testList;
    }

    /**
     * 查询记录条数
     *
     * @return
     */
    public Integer selectCountWhere() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT Count(*) ");
        sb.append("FROM test ");
        sb.append("WHERE 1 = 1 ");
        String sql = sb.toString();
        logger.info("select_sql : " + sql);
        Integer count = super.getJdbcTemplate().queryForObject(sql, Integer.class);
        return count;
    }

    public Test selectObjByPk() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * ");
        sb.append("FROM test ");
        sb.append("WHERE test0 = ? ");//主键
        String sql = sb.toString();
        Test obj = super.getJdbcTemplate().queryForObject(sql, Test.class);
        return obj;
    }

    public String selectStrByPk() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT test0 ");
        sb.append("FROM test ");
        sb.append("WHERE test0 = ? ");//主键
        String sql = sb.toString();
        String str = super.getJdbcTemplate().queryForObject(sql, String.class);
        return str;
    }

    public String byWhere(Test test){
        StringBuffer sb = new StringBuffer();
        // 主键
        if(test.getTest0() != null && test.getTest0().equals("")){
            sb.append(" AND test0 LIKE %" + test.getTest0() + "%");
        }
        if(test.getTest1() != null && test.getTest1().equals("")){
            sb.append(" AND test1 LIKE %" + test.getTest1() + "%");
        }
        if(test.getTest2() != null && test.getTest2().equals("")){
            sb.append(" AND test2 LIKE %" + test.getTest2() + "%");
        }
        if(test.getTest3() != null && test.getTest3().equals("")){
            sb.append(" AND test3 LIKE %" + test.getTest3() + "%");
        }
        if(test.getTest4() != null && test.getTest4().equals("")){
            sb.append(" AND test4 LIKE %" + test.getTest4() + "%");
        }
        if(test.getTest5() != null && test.getTest5().equals("")){
            sb.append(" AND test5 LIKE %" + test.getTest5() + "%");
        }
        if(test.getTest6() != null && test.getTest6().equals("")){
            sb.append(" AND test6 LIKE %" + test.getTest6() + "%");
        }
        if(test.getTest7() != null && test.getTest7().equals("")){
            sb.append(" AND test7 LIKE %" + test.getTest7() + "%");
        }
        if(test.getTest8() != null && test.getTest8().equals("")){
            sb.append(" AND test8 LIKE %" + test.getTest8() + "%");
        }
        if(test.getTest9() != null && test.getTest9().equals("")){
            sb.append(" AND test9 LIKE %" + test.getTest9() + "%");
        }










        return sb.toString();
    }


    public static void main(String[] args) {

        Bean.newBean(Path.BEAN_PATH);
        TestDaoImpl testDaoImpl = (TestDaoImpl) Bean.getBean("testDaoImpl");

        Test test = new Test("001", "1", "2", "3", "4", "5", "6", "7", "8", "9");


        int rows = testDaoImpl.insert(test);

//        int rows = testDaoImpl.update(test);

//        int rows = testDaoImpl.delete(test);

        System.out.println(rows);

        int count = testDaoImpl.selectCountWhere();
        System.out.println(count);

       List<Test> testList = testDaoImpl.selectListWhere();
//        for (int i = 0; i < testList.size(); i++) {
//            System.out.println(testList);
//        }
    }

}

class TestRowMapper implements RowMapper<Test> {
    @Override
    public Test mapRow(ResultSet resultSet, int i) throws SQLException {
        //下标从壹开始
        Test test = new Test();
        test.setTest0(resultSet.getString(1));
        test.setTest1(resultSet.getString(2));
        test.setTest2(resultSet.getString(3));
        test.setTest3(resultSet.getString(4));
        test.setTest4(resultSet.getString(5));
        test.setTest5(resultSet.getString(6));
        test.setTest6(resultSet.getString(7));
        test.setTest7(resultSet.getString(8));
        test.setTest8(resultSet.getString(9));
        test.setTest9(resultSet.getString(10));
        return test;
    }
}
